from django.shortcuts import render, HttpResponse
from Dashboard.models import *
from PeopleInfo.models import *
from django.http.response import JsonResponse, HttpResponseRedirect
from django.template.loader import get_template
from datetime import datetime, date
from django.contrib.auth.decorators import login_required
import os
from django.conf import settings
from Dashboard.functions import *
from django.db.models import Q
import pandas as pd
from Dashboard.code import AccessCheck
from Dashboard.tasks import teacher_access_check_update
from django.core.cache import cache
from DataManagement.utils import MyResponse
from Dashboard.tasks import generate_illness_report
from illness_protection.models import *
import json
import requests
from bs4 import BeautifulSoup

# Create your views here.
sql_11 = '''
SELECT
'{today}' as '日期',
yesterday.username as '学工号',
yesterday.name as '姓名',
yesterday.faculty_name as '单位',
get_14_health_condition(yesterday.username) as '近14天身体状况',
yesterday.tel as '手机号',
get_city_name_helper(yesterday.current_country) as 'yesterday_country',
get_city_name_helper(yesterday.current_province) as 'yesterday_province',
get_city_name_helper(yesterday.current_city) as 'yesterday_city',
get_city_name_helper(yesterday.current_district) as 'yesterday_district',

get_city_name_helper(today.current_country) as 'today_country',
get_city_name_helper(today.current_province) as 'today_province',
get_city_name_helper(today.current_city) as 'today_city',
get_city_name_helper(today.current_district) as 'today_district'
FROM
daily_submit_info_new yesterday
JOIN daily_submit_info_new today ON today.submit_time = '{today}' AND today.username = yesterday.username
WHERE yesterday.submit_time = '{yesterday}'
AND yesterday.current_country = 142 
AND (yesterday.current_district IS NULL OR yesterday.current_district NOT IN ({risk_area}))
AND today.current_district IN ({risk_area})
'''

sql_17 = '''
SELECT
	today.submit_time,
	today.username,
	today.NAME,
	today.faculty_name,
	get_14_health_condition ( today.username ) AS '近14天身体状况',
	today.tel AS '手机号',
get_city_name_helper(yesterday.current_country) as 'yesterday_country',
get_city_name_helper(yesterday.current_province) as 'yesterday_province',
get_city_name_helper(yesterday.current_city) as 'yesterday_city',
get_city_name_helper(yesterday.current_district) as 'yesterday_district',

get_city_name_helper(today.current_country) as 'today_country',
get_city_name_helper(today.current_province) as 'today_province',
get_city_name_helper(today.current_city) as 'today_city',
get_city_name_helper(today.current_district) as 'today_district' 
FROM daily_submit_info_new today 
JOIN daily_submit_info_new yesterday ON  yesterday.username = today.username 
WHERE today.submit_time = '{today}' 
AND yesterday.submit_time = '{yesterday}'
AND today.current_country = 142 
AND (today.current_district IS NULL OR today.current_district NOT IN ({risk_area}) ) 
AND yesterday.current_district IN ({risk_area})
'''


@login_required
def report(request):
    return render(request, template_name='report.html')


def access_check_1(username, check_white_list=True):
    is_ever_been_to_risk_areas, is_has_been_in_guangdong, n_days_in_guangdong, is_has_been_in_dongguan, n_days_in_dongguan, visited_risk_areas, leave_risk_area_time = current_city_aide(username)
    health_condition = health_aide(username)
    # 是否在莞隔离
    have_isolation_in_dg = 0
    # 返莞出行方式
    back_dg_transportation = None
    # 实际返莞日期
    finally_back_time = None
    finally_back_dongguan_days = 0

    temp = DailySubmitInfo.objects.filter(username=username).order_by('-submit_time').first()
    if temp:
        if temp.have_isolation_in_dg:
            have_isolation_in_dg = temp.have_isolation_in_dg
        have_isolation_in_dg_map = {0: '否', 1: '居家隔离', 2: '集中观察', 3: '否，在外地集中隔离或居家观察'}
        have_isolation_in_dg = have_isolation_in_dg_map[have_isolation_in_dg]

    temp = DailySubmitInfo.objects.filter(username=username).filter(~Q(finally_back_dg_transportation=None)).order_by('-submit_time').first()

    if temp:
        back_dg_transportation_map = {0: '火车', 1: '飞机', 2: '轮船', 3: '长途公共巴士', 4: '自驾', 5: '其他'}
        back_dg_transportation = back_dg_transportation_map[temp.finally_back_dg_transportation]

    temp = DailySubmitInfo.objects.filter(username=username).filter(~Q(finally_back_time=None)).order_by('-submit_time').first()
    if temp:
        finally_back_time = datetime.strptime(temp.finally_back_time, '%Y-%m-%d')
        interval = datetime.now() - finally_back_time
        finally_back_dongguan_days = interval.days
    else:
        finally_back_dongguan_days = n_days_in_dongguan

    code = 600200
    message = '准入'

    # 数据
    data = {
        'is_ever_been_to_risk_areas': is_ever_been_to_risk_areas,
        'is_has_been_in_guangdong': is_has_been_in_guangdong,
        'n_days_in_guangdong': n_days_in_guangdong,
        'n_days_in_dongguan': finally_back_dongguan_days,
        'health_condition': health_condition,
        'have_isolation_in_dg': have_isolation_in_dg,
        'back_dg_transportation': back_dg_transportation,
        'visited_risk_areas': ','.join(visited_risk_areas) if visited_risk_areas else None,
        'leave_risk_area_time': leave_risk_area_time
    }

    is_in_white_list = WhiteList.objects.filter(username=username).first()

    if is_in_white_list and check_white_list:
        return JsonResponse({'code': code, 'message': message, 'data': data}, json_dumps_params={'ensure_ascii': False})

    # 隔离期间，不准入
    if have_isolation_in_dg != '否':
        code = 600603
        message = '隔离期间，禁止返校'
    # 身体不适，不准入
    elif health_condition != '近14天身体健康':
        code = 600601
        message = '禁止入内，身体健康未满14天'
    # 疫情重点关注地区人员或通过公共交通返莞的人员，返莞未满14天，不准入
    elif (back_dg_transportation in (None, '火车', '飞机', '轮船', '长途公共巴士', '其他') or is_ever_been_to_risk_areas) and n_days_in_dongguan < 14:
        code = 600602
        message = '禁止入内，返回东莞未满14天'
    elif back_dg_transportation == '自驾' and is_has_been_in_guangdong:
        code = AccessCheck.ACCESS
        message = '准入'
    # 其余人员，返莞未满7天，不准入
    elif n_days_in_dongguan < 7:
        code = 600602
        message = '禁止入内，返回东莞未满7天'

    return {'code': code, 'message': message, 'data': data}


def response(data=[], message='准入', code=AccessCheck.ACCESS):
    return JsonResponse({'code': code, 'message': message, 'data': data}, json_dumps_params={'ensure_ascii': False})


def teacher_handle(data):
    if (data['use_public_transport'] or data['is_ever_been_to_risk_areas']) and data['finally_back_dongguan_days'] < 14:
        return response(data=data, message='禁止入内，返回东莞未满14天', code=AccessCheck.UN_ENOUGH_DAY)
    elif data['is_has_been_in_guangdong'] and not data['use_public_transport']:
        return response(data=data, message='准入', code=AccessCheck.ACCESS)
    elif data['finally_back_dongguan_days'] < 7:
        return response(data=data, message='禁止入内，返回东莞未满7天', code=AccessCheck.UN_ENOUGH_DAY)
    else:
        return response(data=data, message='不准入，请联系叶嘉欣（654582）', code=AccessCheck.NOT_ALLOWED)


def batch(number):
    start = {
        1: '2020-05-17',
        2: '2020-05-22',
        3: '2020-06-05',
    }
    end = {
        1: '2020-05-31',
        2: '2020-05-31',
        3: '2020-06-13',
    }
    now = date.today().strftime("%Y-%m-%d")
    try:
        if now >= start.get(number) and now <= end.get(number):
            return True
        else:
            return False
    except Exception as e:
        return False


def student_handle(data):
    if data['is_ever_been_to_risk_areas']:
        return response(data=data, message='疫情重点关注地区禁止返校', code=AccessCheck.IN_RISK_AREA)
    if not data['is_finished_questionnaire']:
        return response(data=data, message='未完成问卷', code=AccessCheck.UNFINISHED_QUESTIONNAIRE)
    # 获取批次信息
    if not batch(data['batch_number']):
        return response(data=data, message='不符合批次要求', code=AccessCheck.NOT_UP_TO_BATCH_REQUIREMENTS)

    return response(data)


def color(request):
    username = request.GET['username']
    if not username:
        return response(message='参数username不能为空', code=AccessCheck.PARAMETER_ERROR)

    if WhiteList.objects.filter(username=username).first():
        return response(message='准入', code=AccessCheck.ACCESS)

    user = PeopleAtSchool.objects.filter(xh=username).first()
    if not user:
        return response([], '疫情网报系统无该用户数据', AccessCheck.WITHOUT_USER)
    data = DailySubmitInfo.access_check(username)

    # 隔离期间，不准入
    if data['is_isolation_in_dg']:
        return response(data=data, message='隔离期间，禁止返校', code=AccessCheck.ISOLATED)
    # 身体不适，不准入
    elif data['continuous_health_days'] < 14:
        return response(data=data, message='禁止入内，身体健康未满14天', code=AccessCheck.UN_ENOUGH_DAY)

    elif user.type in ('teacher', 'foreign teacher'):
        return teacher_handle(data)
    else:
        return student_handle(data)


def access_check(request):
    '''
    authorization = request.META.get('HTTP_AUTHORIZATION')
    print(authorization)

    if not authorization:
        res = {'cdoe': 600401, 'message': '参数错误', 'data': []}
        return JsonResponse(res, json_dumps_params={'ensure_ascii': False})
    '''

    username = request.GET['username']
    if not username:
        res = {'code': 600401, 'message': '参数username不能为空', 'data': []}
        return JsonResponse(res, json_dumps_params={'ensure_ascii': False})

    user = PeopleAtSchool.objects.filter(xh=username).first()
    if not user:
        res = {'code': 600402, 'message': '疫情网报系统无该用户数据', 'data': []}
        return JsonResponse(res, json_dumps_params={'ensure_ascii': False})

    res = access_check_1(username)
    res['data']['type'] = user.type

    return JsonResponse(res, json_dumps_params={'ensure_ascii': False})


def access_check_generate_report():
    from DataCenter.code import Department
    data = []
    i = 1
    for people in PeopleAtSchool.objects.all():

        result = access_check_1(people.xh, check_white_list=False)

        department = Department.objects.filter(value=people.yxdm).first()

        xiao_ban_type = XiaobanType.objects.filter(username=people.xh).first()
        if xiao_ban_type:
            user_type = xiao_ban_type.type
        else:
            user_type = '暂无'
        department_title = None
        if department:
            department_title = department.label

        data.append([
            people.xh,
            people.xm,
            user_type,
            department_title,
            result['message'],
            result['data']['health_condition'],
            '是' if result['data']['is_ever_been_to_risk_areas'] else '否',
            '是' if result['data']['is_has_been_in_guangdong'] else '否',
            result['data']['n_days_in_guangdong'],
            result['data']['n_days_in_dongguan'],
            result['data']['have_isolation_in_dg'],
            result['data']['back_dg_transportation'],
            result['data']['visited_risk_areas'],
            result['data']['leave_risk_area_time']
        ])
        # print(result)
        print('处理第{}个：{} {} {} {}'.format(i, people.xh, people.xm, user_type, result['message']))
        i += 1

    excel = pd.DataFrame(data)
    excel.columns = ['学工号', '姓名', '用户类型', '单位', '是否准入', '身体状况', '是否去过疫情重点关注地区', '是否一直在广东',
                     '返回广东天数', '返回东莞天数', '是否在莞隔离', '返莞交通方式', '到访过的重点地区', '上一次离开重点地区的时间']
    filename = '{}.xlsx'.format(datetime.now().strftime('%Y-%m-%d'))
    excel.to_excel(filename, encoding='UTF-8', index=False)
    return True


def generate_risk_rank_sheet():
    SQL_7


def helper(data):
    arr1, arr2 = [], []
    for col1, col2 in data:
        arr1.append(col1)
        arr2.append(col2)
    return arr1, arr2


def doc(request):
    res = generate_illness_report.delay()
    # 任务逻辑
    return HttpResponseRedirect('/datacenter/result/{}'.format(res.task_id))


def test(request):
    res = generate_illness_report()
    # 任务逻辑
    return HttpResponse(res)


def func1(request, today):
    yesterday = (datetime.strptime(today, '%Y-%m-%d') - timedelta(days=1)).strftime('%Y-%m-%d')

    risk_area = ','.join(item.region for item in ImportantArea.objects.filter(delete=0))
    city_map = {}
    for item in ProvinceCityNew.objects.all():
        city_map[item.id] = item.name

    SQL_1 = sql_11.format(today=today, yesterday=yesterday, risk_area=risk_area)
    SQL_2 = sql_17.format(today=today, yesterday=yesterday, risk_area=risk_area)

    data1 = sql_execute_fetchall(SQL_1)

    context = {
        'sql_11': data1,
        'sql_17': sql_execute_fetchall(SQL_2),
    }
    return render(request, template_name='Dashboard/func1.html', context=context)


def echart(request, submit_time=None):
    def echart_func16(submit_time):
        sql = '''
SELECT
get_province_name(daily_submit_info_new.current_province),
daily_submit_info_new.current_province,
count(*)
FROM
daily_submit_info_new
WHERE submit_time='{}' and current_province is not null
GROUP BY daily_submit_info_new.current_province
        '''

        sql_2 = '''
SELECT
province_city_new.name,
count(*)
FROM
daily_submit_info_new
LEFT JOIN province_city_new ON province_city_new.id = daily_submit_info_new.current_city
WHERE submit_time='{}' and current_province = {}
GROUP BY daily_submit_info_new.current_city
        '''
        china, provinces = [], {}
        for province_name, province_id, value in sql_execute_fetchall(sql.format(submit_time)):
            china.append({'name': province_name, 'value': value})
            provinces[province_name] = []

            for city_name, city_value in sql_execute_fetchall(sql_2.format(submit_time, province_id)):
                provinces[province_name].append({'name': city_name, 'value': city_value})

        return china, provinces
    china, province = echart_func16(submit_time)
    context = {
        'china': china,
        'province': province
    }

    def save_file(html, filepath):
        file = open(filepath, 'w', encoding='utf-8')
        file.write(html)
        file.close()

    template = get_template('Dashboard/echart.html')
    html = template.render(context)
    filename = os.path.join(settings.STATIC_ROOT, 'echart.html')
    save_file(html, filename)

    return render(request, template_name='Dashboard/echart.html', context=context)


def teacher_access_check_task(request):
    username = request.GET['username']
    res = teacher_access_check_update.delay(username)
    data = {
        'task_id': res.task_id,
        'target_url': 'http://console.data.dgut.edu.cn/datacenter/task/{}'.format(res.task_id)
    }
    return MyResponse.success(data)


def teacher_access_check(request):
    username = request.GET['username']
    res = cache.get('access_check_{}'.format(username))

    # 当缓存失效时，重新根据数据计算一次
    if not res:
        data = teacher_access_check_update(username)
        return MyResponse.success(data['data'], data['message'], data['code'])
    else:
        return MyResponse.success(res, res['reason'], res['code'])


def teacher_access_remove_cache(request):
    username = request.GET['username']
    cache.delete('access_check_{}'.format(username))
    return MyResponse.success(None, "缓存删除成功", AccessCheck.ACCESS)



def get_risk_city(request):
    response = requests.get("http://www.lubulai.com/")
    response.encoding = "UTF-8"
    soup = BeautifulSoup(response.text, 'html.parser')
    script_string = soup.find("body").find("script").string
    script_string = script_string.strip().replace('\r\n', '')
    data_text = script_string.split('var data =')[1].split(';            var datanew=')[0].strip()
    new_data_text = script_string.split(';            var datanew=')[1].split(';                 var datahigh=')[0].strip()
    high_data_text = script_string.split(';                 var datahigh=')[1].split(';                 var datalow=')[0].strip()
    data = json.loads(data_text)
    new_data = json.loads(new_data_text)
    high_data = json.loads(high_data_text)
    result = data + new_data + high_data
    return MyResponse.success(result, "操作成功", "00000")
